<?php
class KetquasController extends AppController {

	var $name = 'Ketquas';
	var $uses = array('Ketqua','Lop','Lophp','Hknk','Bangdiem','Sinhvien','Nhomhp','Monthamgianhom');
	var $helpers = array('flashChart','Js');
	var $components = array('RequestHandler');

	function index() {
		$this->Ketqua->recursive = 0;
		$this->set('ketquas', $this->paginate());
	}

	function view($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid ketqua', true));
			$this->redirect(array('action' => 'index'));
		}
		$this->set('ketqua', $this->Ketqua->read(null, $id));
	}

	function add() {
		if (!empty($this->data)) {
			$this->Ketqua->create();
			if ($this->Ketqua->save($this->data)) {
				$this->Session->setFlash(__('Lưu thành công!', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('Không thể lưu dữ liệu. Kiểm tra lại!', true));
			}
		}
		$lophps = $this->Ketqua->Lophp->find('list');
		$sinhviens = $this->Ketqua->Sinhvien->find('list');
		$this->set(compact('lophps', 'sinhviens'));
	}

	function edit($id = null) {
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid ketqua', true));
			$this->redirect(array('action' => 'index'));
		}
		if (!empty($this->data)) {
			if ($this->Ketqua->save($this->data)) {
				$this->Session->setFlash(__('Lưu thành công!', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('Không thể lưu dữ liệu. Kiểm tra lại!', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Ketqua->read(null, $id);
		}
		$lophps = $this->Ketqua->Lophp->find('list');
		$sinhviens = $this->Ketqua->Sinhvien->find('list');
		$this->set(compact('lophps', 'sinhviens'));
	}

	function delete($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid id for ketqua', true));
			$this->redirect(array('action'=>'index'));
		}
		if ($this->Ketqua->delete($id)) {
			$this->Session->setFlash(__('Ketqua deleted', true));
			$this->redirect(array('action'=>'index'));
		}
		$this->Session->setFlash(__('Ketqua was not deleted', true));
		$this->redirect(array('action' => 'index'));
	}

	function thongkekq($lop =null, $hkdt = null,$nkdt = null)
	{
		if(!empty($this->data))
		{
			$hkdt = $this->data['Ketqua']['hk'];
			$nkdt = $this->data['Ketqua']['nienkhoa'];
			$lop = $this->data['Ketqua']['lop'];
			$this->redirect(array('controller'=>'ketquas','action'=>'thongkekq',$lop,$hkdt,$nkdt));
		}
		if(!empty($lop))
		{
			$lhps = $this->Lophp->find('list',array('recursive'=>2,'conditions'=>array('Hknk.hocky'=>$hkdt,'Hknk.nienkhoa'=>$nkdt)));
			$sql = "SELECT diem, COUNT( sinhvien_id ) as soluong
					FROM (SELECT sinhvien_id, SUM( diemtinh ) AS diemtinh, SUM( soTC ) AS sotctinh, SUM( diemtinh ) / SUM( soTC ) AS diem
							FROM (SELECT sinhvien_id, mon.soTC * bd.diemso AS diemtinh, mon.soTC, bd.diemso
									FROM  `ketquas` kq, lophps lhp, mons mon, bangdiems bd, sinhviens sv
									WHERE kq.sinhvien_id= sv.id
									AND kq.lophp_id = lhp.id
									AND kq.tichLuy = bd.id
									AND lhp.mon_id = mon.id
									AND sv.lop_id =".$lop."
									AND lhp.id IN (". implode(',',$lhps).") 
									GROUP BY sinhvien_id, mon_id)a
						GROUP BY sinhvien_id) Ketqua
					GROUP BY diem";
			$ketqua = $this->Ketqua->query($sql);
			$ketquas =array();
			$ketquas[0]['Ketqua']['ten'] ='Xuất sắc';
			$ketquas[1]['Ketqua']['ten'] ='Giỏi';
			$ketquas[2]['Ketqua']['ten'] ='Khá';
			$ketquas[3]['Ketqua']['ten'] ='Trung bình';
			$ketquas[4]['Ketqua']['ten'] ='Yếu';
			$ketquas[0]['Ketqua']['sl'] =0;
			$ketquas[1]['Ketqua']['sl'] =0;
			$ketquas[2]['Ketqua']['sl'] =0;
			$ketquas[3]['Ketqua']['sl'] =0;
			$ketquas[4]['Ketqua']['sl'] =0;
			foreach ($ketqua as $value)
			{
				if($value['Ketqua']['diem'] > 3.6)
					$ketquas[0]['Ketqua']['sl'] += $value[0]['soluong'];;
				if($value['Ketqua']['diem'] >=3.2 && $value['Ketqua']['diem'] <=3.59)
					$ketquas[1]['Ketqua']['sl'] += $value[0]['soluong'];;
				if($value['Ketqua']['diem'] >=2.5 && $value['Ketqua']['diem'] <=3.19)
					$ketquas[2]['Ketqua']['sl'] += $value[0]['soluong'];
				if($value['Ketqua']['diem'] >=2 && $value['Ketqua']['diem'] <=2.49)
					$ketquas[3]['Ketqua']['sl'] += $value[0]['soluong'];
				if($value['Ketqua']['diem'] <2)
					$ketquas[4]['Ketqua']['sl'] += $value[0]['soluong'];
			}
		}
		$giangvien_id = 1;//$this->Session->read('giangvien_id');
		$lops = $this->Lop->find('list',array('fields'=>array('lop')));
		if(!empty($lops))
		{
			$lophps = $this->Ketqua->find('list',array('recursive'=>1,'fields'=>array('lophp_id'),'conditions'=>array('Sinhvien.lop_id'=>key($lops))));
			$hknks = $this->Lophp->find('list',array('fields'=>array('hknk_id'),'conditions'=>array('id'=>$lophps)));
			$namhoc = $this->Hknk->find('list',array('conditions'=>array('id'=>$hknks),'fields'=>array('nienkhoa','nienkhoa'),'group'=>'nienkhoa','order'=>array('id DESC')));
			$hk = $this->Hknk->find('list',array('fields'=>array('hocky','hocky'),'group'=>'hocky','order'=>array('hocky ASC')));
		}
		$this->set(compact('lops','namhoc','hk','ketquas','lop','hkdt','nkdt'));
	}

	function timds()
	{
		$giangvien_id = 1;//$this->Session->read('giangvien_id');///
		$lops = $this->Lop->find('list',array('fields'=>array('lop')));
		if(!empty($lops))
		{
			$lophps = $this->Ketqua->find('list',array('recursive'=>1,'fields'=>array('lophp_id'),'conditions'=>array('Sinhvien.lop_id'=>key($lops))));
			$hknks = $this->Lophp->find('list',array('fields'=>array('hknk_id'),'conditions'=>array('id'=>$lophps)));
			$namhoc = $this->Hknk->find('list',array('conditions'=>array('id'=>$hknks),'fields'=>array('nienkhoa','nienkhoa'),'group'=>'nienkhoa','order'=>array('id DESC')));
			$hk = $this->Hknk->find('list',array('fields'=>array('hocky','hocky'),'group'=>'hocky','order'=>array('hocky ASC')));
		}
		$this->set(compact('lops','namhoc','hk'));
	}
	
	function updateds()
	{
			$this->layout = 'ajax';

			$hkdt = $this->data['Ketqua']['hk'];
			$nkdt = $this->data['Ketqua']['nienkhoa'];
			$lop = $this->data['Ketqua']['lop'];
			$dau = $this->data['Ketqua']['chondau'];
			$diem = $this->data['Ketqua']['diem'];
			$lhps = $this->Lophp->find('list',array('recursive'=>2,'conditions'=>array('Hknk.hocky'=>$hkdt,'Hknk.nienkhoa'=>$nkdt)));
			if(!empty($lop) && !empty($lhps))
			{
			$sql = "SELECT Sinhvien.id,Sinhvien.mssv,Sinhvien.tenSV, diem
					FROM (SELECT sinhvien_id, SUM( diemtinh ) AS diemtinh, SUM( soTC ) AS sotctinh, SUM( diemtinh ) / SUM( soTC ) AS diem
							FROM (SELECT sinhvien_id, mon.soTC * bd.diemso AS diemtinh, mon.soTC, bd.diemso
									FROM  `ketquas` kq, lophps lhp, mons mon, bangdiems bd, sinhviens sv
									WHERE kq.sinhvien_id= sv.id
									AND kq.lophp_id = lhp.id
									AND kq.tichLuy = bd.id
									AND lhp.mon_id = mon.id
									AND sv.lop_id =".$lop."
									AND lhp.id IN (". implode(',',$lhps).") 
									GROUP BY sinhvien_id, mon_id)a
						GROUP BY sinhvien_id) Ketqua, sinhviens Sinhvien
						where Sinhvien.id=Ketqua.sinhvien_id and Ketqua.diem ".$dau." ".$diem;
			$ketqua = $this->Ketqua->query($sql);
			$this->set(compact('ketqua'));
			}
	}

	function thongketyle()
	{
		$sinhvien_id = 1;//session
		$hks = $this->Hknk->find('list',array('fields'=>array('hocky','hocky'),'group'=>'hocky'));
		$nks = $this->Hknk->find('list',array('fields'=>array('nienkhoa','nienkhoa'),'group'=>'nienkhoa','order'=>array('id DESC')));
		$ng =  $this->Sinhvien->find('first',array('recursive'=>1,'conditions'=>array('Sinhvien.id'=>$sinhvien_id)));
		if(!empty($ng))
		{
			//debug($ng);
			$nganh_id = $ng['Lop']['nganh_id'];
			//debug($nganh_id);
			$nhom = $this->Nhomhp->find('all',array('recursive'=>0,'fields'=>array('id','nhomhp_id','tenNhom'),'conditions'=>array('Ctdt.nganh_id'=>$nganh_id)));
			
			$nhoms = array();
			foreach ($nhom as $row)
			{
				$check = false;
				foreach ($nhom as $r)
				{
					if($r['Nhomhp']['nhomhp_id']==$row['Nhomhp']['id'])
						$check = true;
				}
				if($check===false)
					$nhoms[$row['Nhomhp']['id']] =$row['Nhomhp']['tenNhom'];
			}
			
		}
		$this->set(compact('hks','nks','nhoms'));
	}
	
	function updatetyle()
	{
		$this->layout = 'ajax';
		$hkdt = $this->data['Ketqua']['hk'];
		$nkdt = $this->data['Ketqua']['nk'];
		$nhom = $this->data['Ketqua']['nhomhp'];//SELECT mon.id,mon.tenMon,mon.soTC,sinhvien_id, SUM( diemtinh ) AS diemtinh, SUM(a.soTC ) AS sotctinh, SUM( diemtinh ) / SUM( a.soTC ) AS diem FROM (
		$mons = $this->Monthamgianhom->find('list',array('conditions'=>array('nhomhp_id'=>$nhom),'fields'=>array('mon_id','mon_id')));
		$lhps = $this->Lophp->find('list',array('recursive'=>2,'conditions'=>array('Hknk.hocky'=>$hkdt,'Hknk.nienkhoa'=>$nkdt)));
		$str = "SELECT mon.id,bd.diemchu,count(bd.diemchu) as sodiemchu
				FROM  `ketquas` kq, lophps lhp, mons mon, sinhviens sv, bangdiems bd
									WHERE kq.sinhvien_id= sv.id
									AND kq.lophp_id = lhp.id
									AND bd.id = kq.tichLuy
									AND lhp.mon_id = mon.id
									AND lhp.id IN (". implode(',',$lhps).") 
									AND mon.id IN (".implode(',',$mons).")
				GROUP BY mon_id,bd.diemchu";
		
		$str1 = "SELECT mon.id,mon.mamom,mon.tenMon,mon.soTc,count(sv.id) as sosv
				FROM  `ketquas` kq, lophps lhp, mons mon, sinhviens sv
				WHERE kq.sinhvien_id= sv.id
					AND kq.lophp_id = lhp.id
					AND lhp.mon_id = mon.id
					AND lhp.id IN (". implode(',',$lhps).") 
					AND mon.id IN (".implode(',',$mons).")
				GROUP BY mon.id";
		$ketqua = $this->Ketqua->query($str);
		$kq = $this->Ketqua->query($str1);
		$this->set(compact('ketqua','kq'));
	}
	
	
}
function color()
{
	mt_srand((double)microtime()*1000000);
	$c = '';
	while(strlen($c)<6)
	{
		$c .= sprintf("%02X", mt_rand(0, 255));
	}
	return  $c;
}